﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Text.RegularExpressions;
namespace QuanLyBanHang.DAL
{
    public class DataHelper
    {
        public static SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["strConn"].ToString());
        public static void Open()
        {
            if (con.State != ConnectionState.Open)
            {
                con.Open();
            }
        }
        public static void Close()
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
        public static DataTable Load(string sql)//load dữ liệu không tham số
        {
            Open();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sql, con);
            SqlDataReader read = cmd.ExecuteReader();
            dt.Load(read);
            Close();
            return dt;
        }
        public static DataTable Load(SqlParameter[] para, string sql)//load dữ liệu có tham số từ store procedure
        {
            Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            SqlDataReader read = cmd.ExecuteReader();
            DataTable dt = new DataTable();
            dt.Load(read);
            Close();
            return dt;
        }
        public static int Update(SqlParameter[] para, string sql)//phương thức thực thi insert, update, delete
        {
            Open();
            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(para);
            cmd.Transaction = tran;
            try
            {
                int thucthi = cmd.ExecuteNonQuery();
                tran.Commit();
                return thucthi;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                Close();
                if (tran != null) tran.Dispose();
                if (cmd != null) cmd.Dispose();
            }
        }

        public static bool isValidEmail(string inputEmail)
        {
            string strRegex = @"^([a-zA-Z0-9_\-\.]+)@((\[[0-9]{1,3}" +
                  @"\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([a-zA-Z0-9\-]+\" +
                  @".)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$";
            Regex re = new Regex(strRegex);
            if (re.IsMatch(inputEmail))
                return (true);
            else
                return (false);
        }

        public static bool CheckAll(string key, string column, string table)
        {
            Open();
            string sql = string.Format("select {0} from {1} where {0} = '{2}'",column,table,key);
            SqlCommand cmd = new SqlCommand(sql, con);
            object kq = cmd.ExecuteScalar();
            if (kq == null)
            {
                Close();
                return true;
            }
            else
            {
                Close();
                return false;
            }
        }
    }
}

